﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace VoteInformationSystem.Models
{
    public class NpgsqlBundeslandRepository : IBundeslandRepository
    {
        public Bundesland Get(int Id)
        {
            Bundesland bland = null;

            using (NpgsqlConnection pgConnection = NpgsqlConnectionProvider.GetConnection())
            using (NpgsqlCommand command = new NpgsqlCommand("select \"idbundesland\", \"bname\", \"idbundestagswahl\" from \"Bundeslaender\" where \"idbundesland\" = :id", pgConnection))
            {
                command.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
                command.Prepare();
                command.Parameters.First<NpgsqlParameter>().Value = Id;
                using (NpgsqlDataReader dataReader = command.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        bland = new Bundesland() { Id = dataReader.GetInt32(0), Name = dataReader.GetString(1), IdBundestagswahl = dataReader.GetInt32(2) };
                    }
                }
            }

            return bland;
        }

        public IEnumerable<Bundesland> GetByBundestagswahl(int WahlId)
        {
            IList<Bundesland> bundeslaender = new List<Bundesland>();

            using (NpgsqlConnection pgConnection = NpgsqlConnectionProvider.GetConnection())
            using (NpgsqlCommand command = new NpgsqlCommand("select \"idbundesland\", \"bname\", \"idbundestagswahl\" from \"Bundeslaender\" where \"idbundestagswahl\" = :id", pgConnection))
            {
                command.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
                command.Prepare();
                command.Parameters.First<NpgsqlParameter>().Value = WahlId;
                using (NpgsqlDataReader dataReader = command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        bundeslaender.Add(new Bundesland() { Id = dataReader.GetInt32(0), Name = dataReader.GetString(1), IdBundestagswahl = dataReader.GetInt32(2) });
                    }
                }
            }

            return bundeslaender;
        }

        public IEnumerable<Partei> GetParteien(int LandId)
        {
            IList<Partei> parteien = new List<Partei>();

            using (NpgsqlConnection pgConnection = NpgsqlConnectionProvider.GetConnection())
            using (
                NpgsqlCommand command =
                    new NpgsqlCommand(
                        "select distinct \"idpartei\", \"pname\" from \"Bundeslaender\" join \"Landeslisten\" using (idbundesland) join \"Personen\" using (idperson) join \"Parteien\" using (idpartei) where \"idbundesland\" = :id",
                        pgConnection))
            {
                command.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
                command.Prepare();
                command.Parameters.First<NpgsqlParameter>().Value = LandId;
                using (NpgsqlDataReader dataReader = command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        parteien.Add(new Partei() {Id = dataReader.GetInt32(0), Name = dataReader.GetString(1)});
                    }
                }
            }
            return parteien;
        }
    }
}
